CREATE OR REPLACE TRIGGER BDADIncrementarEscuelas
   BEFORE INSERT
     ON BDADEscuelas
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT BDADSEscuelas.nextval INTO :NEW.idEscuela FROM dual;
   END;

INSERT INTO BDADEscuelas (Facultad, Nombre) VALUES (1, 'ESCUELA DE CIENCIAS AGRÍCOLAS');
INSERT INTO BDADEscuelas (Facultad, Nombre) VALUES (1, 'ESCUELA DE CIENCIAS ANIMALES');
INSERT INTO BDADEscuelas (Facultad, Nombre) VALUES (1, 'IALL');
INSERT INTO BDADEscuelas (Facultad, Nombre) VALUES (3, 'ESCUELA DE ENFERMERÍA');
INSERT INTO BDADEscuelas (Facultad, Nombre) VALUES (4, 'ESCUELA DE PEDAGOGÍA Y BELLAS ARTES');
INSERT INTO BDADEscuelas (Facultad, Nombre) VALUES (5, 'ESCUELA DE CIENCIAS ECONÓMICAS');
INSERT INTO BDADEscuelas (Facultad, Nombre) VALUES (6, 'ESCUELA DE INGENIERÍA');
INSERT INTO BDADEscuelas (Facultad, Nombre) VALUES (6, 'DEPARTAMENTO DE CIENCIAS BÁSICAS');


CREATE OR REPLACE TRIGGER BDADIncrementarDocentes
   BEFORE INSERT
     ON BDADDocentes
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT BDADSDocentes.nextval INTO :NEW.idDocentes FROM dual;
   END;

CREATE OR REPLACE FUNCTION BDADBuscarDocentePorCedula(p1 IN BDADDocentes.Cedula%TYPE) 
return NUMBER 
IS
RidDocente NUMBER;
    BEGIN
    SELECT idDocentes INTO RidDocente FROM BDADDocentes WHERE Cedula = p1;
    return (riddocente);
    EXCEPTION
        WHEN OTHERS THEN
            RidDocente := 0;
            return (riddocente);
    END;

CREATE OR REPLACE TRIGGER BDADIncrementarFormacion
   BEFORE INSERT
     ON BDADFormacionAcademica
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT DBADSFormacionAcademica.nextval INTO :NEW.idFormacionAcademica FROM dual;
   END;


CREATE OR REPLACE TRIGGER BDADIncrementarExperiencia
   BEFORE INSERT
     ON BDADExperiencia
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT BDADSExperiencia.nextval INTO :NEW.idExperiencia FROM dual;
   END;


CREATE OR REPLACE TRIGGER BDADIncrementarPonencias
   BEFORE INSERT
     ON BDADPonencias
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT BDADSPonencia.nextval INTO :NEW.idPonencias FROM dual;
   END;

CREATE OR REPLACE TRIGGER BDADIncrementarLibros
   BEFORE INSERT
     ON BDADLibros
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT BDADSLibro.nextval INTO :NEW.idLibros FROM dual;
   END;


CREATE OR REPLACE TRIGGER BDADIncrementarArticulos
   BEFORE INSERT
     ON BDADArticulos
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT BDADSArticulo.nextval INTO :NEW.idArticulos FROM dual;
   END;

CREATE OR REPLACE TRIGGER BDADIncrementarPlanta
   BEFORE INSERT
     ON BDADPlanta
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT BDADSPlanta.nextval INTO :NEW.idPlanta FROM dual;
   END;


CREATE OR REPLACE TRIGGER BDADIncrementarOcasionales
   BEFORE INSERT
     ON BDADOcasionales
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT BDADSOcasionales.nextval INTO :NEW.idOcasional FROM dual;
   END;


CREATE OR REPLACE TRIGGER BDADIncrementarCatedraticos
   BEFORE INSERT
     ON BDADCatedraticos
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT BDADSCatedraticos.nextval INTO :NEW.idCatedratico FROM dual;
   END;


CREATE OR REPLACE TRIGGER BDADIncrementarUsuarios
   BEFORE INSERT
     ON BDADUsuarios
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT BDADSUsuarios.nextval INTO :NEW.idUsuario FROM dual;
   END;

INSERT INTO BDADUsuarios (Nombre, Apellidos, Usuario, Passwd, Tipo) VALUES ('Brayan David','Arias', 'brayitan', 'brayitan123', 'A');
INSERT INTO BDADUsuarios (Nombre, Apellidos, Usuario, Passwd, Tipo) VALUES ('Administrador', 'Administrador', 'admin', 'admin', 'A');

CREATE OR REPLACE FUNCTION BDADIniciarSesion(p1 IN BDADUsuarios.Usuario%TYPE, p2 IN BDADUsuarios.Passwd%TYPE) 
return VARCHAR2 
IS
RNombre VARCHAR2(100);
    BEGIN
    SELECT nombre||' '||apellidos||'-'||tipo into RNombre FROM BDADusuarios where usuario=p1 and passwd=p2;
    return (RNombre);
    EXCEPTION
        WHEN OTHERS THEN
            RNombre := '0';
            return (RNombre);
    END;
